using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Data.SqlClient;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class report_Database : System.Web.UI.Page
{
    private SqlConnection myConnection;
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            string connString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            Response.Write(connString);
            myConnection = new SqlConnection(connString);
            ViewImportResult();
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message + "<br /><br />" + ex.StackTrace);
        }
    }

    private void ViewImportResult()
    {
        myConnection.Open();
        string sql = "SELECT * FROM Patient order by ID desc";

        SqlCommand myCommand = new SqlCommand(sql, myConnection);

        DataSet myDataSet = new DataSet();

        SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
        myAdapter.Fill(myDataSet);

        GridView1.DataSource = myDataSet;
        GridView1.DataBind();

        myConnection.Close();
    }

    protected void btnUpdateTable_Click(object sender, EventArgs e)
    {
        try
        {
            myConnection.Open();
            //string insertSql = "CREATE TABLE [dbo].[Patient]([ID] [int] IDENTITY(1,1) NOT NULL,[Code] [nvarchar](10) NULL,[PatientName] [nvarchar](50) NULL,[CompanyName] [nvarchar](100) NULL,[Pass] [nvarchar](10) NULL,[FolderPath] [nvarchar](max) NULL,CONSTRAINT [PK_Patient] PRIMARY KEY CLUSTERED ([ID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]";
            string insertSql = "Alter TABLE [dbo].[Patient] Add [Log] [nvarchar](50) NULL";
            SqlCommand myCommand = new SqlCommand(insertSql, myConnection);
            myCommand.ExecuteNonQuery();
            myConnection.Close();
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message + "<br /><br />" + ex.StackTrace);
            //myConnection.Close();
        }
    }
    protected void btnResetTable_Click(object sender, EventArgs e)
    {
        try
        {
            myConnection.Open();
            string insertSql = "TRUNCATE TABLE Patient";
            SqlCommand myCommand = new SqlCommand(insertSql, myConnection);
            myCommand.ExecuteNonQuery();
            ViewImportResult();
            myConnection.Close();
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message + "<br /><br />" + ex.StackTrace);
            //myConnection.Close();
        }
    }
}
